package com.example.studentvotesystem.mapper;

import com.example.studentvotesystem.entity.ScenicSpot;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface ScenicSpotMapper {
    @Insert("INSERT INTO scenic_spot(name, description, location, image_url) VALUES(#{name}, #{description}, #{location}, #{imageUrl})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(ScenicSpot scenicSpot);

    @Update("UPDATE scenic_spot SET name=#{name}, description=#{description}, location=#{location}, image_url=#{imageUrl} WHERE id=#{id}")
    int update(ScenicSpot scenicSpot);

    @Delete("DELETE FROM scenic_spot WHERE id=#{id}")
    int delete(Integer id);

    @Select("SELECT * FROM scenic_spot WHERE id = #{id}")
    ScenicSpot selectById(Integer id);

    @Select("SELECT * FROM scenic_spot ORDER BY create_time DESC")
    List<ScenicSpot> selectAll();

    @Select("SELECT s.id as id,s.name as name,s.description as description,s.location as location,s.image_url as imageUrl,s.create_time as createTime, COUNT(v.id) as voteCount FROM scenic_spot s LEFT JOIN vote_record v ON s.id = v.spot_id GROUP BY s.id ORDER BY voteCount DESC")
    List<ScenicSpot> selectAllWithVoteCount();
}
